package com.mbc.common.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.entities.Branch;
import com.mbc.common.entities.BranchRpt;
import com.mbc.common.data.BranchDataList;
import com.mbc.common.data.ReferenceData;

public class BranchDAOImpls extends DAO<Branch> implements BranchDAO {

	public BranchDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "Branch";
	}

	@Override
	public void create(Branch entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( BranchId, ParentId, BranchTypeId, BranchCode, Name, MyanmarName, Phone, Fax, Email, Address, City, Country, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.setLong(i++, entity.getParentId());
		pstmt.setLong(i++, entity.getBranchTypeId());
		pstmt.setString(i++, entity.getBranchCode());
		pstmt.setString(i++, entity.getName());
		pstmt.setString(i++, entity.getMyanmarName());
		pstmt.setString(i++, entity.getPhone());
		pstmt.setString(i++, entity.getFax());
		pstmt.setString(i++, entity.getEmail());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setString(i++, entity.getCity());
		pstmt.setString(i++, entity.getCountry());
		pstmt.setDate(i++, new java.sql.Date(entity.getCreatedDate().getTime()));
		pstmt.setLong(i++, entity.getCreatedBy());
		pstmt.setDate(i++,
				new java.sql.Date(entity.getModifiedDate().getTime()));
		pstmt.setLong(i++, entity.getModifiedBy());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(Branch entity) throws SQLException {
		query = "update "
				+ table_name
				+ " set  ParentId = ?, BranchTypeId = ?, BranchCode = ?, Name = ?, MyanmarName = ?, Phone = ?, Fax = ?, Email = ?, Address = ?, City = ?, Country = ?, ModifiedDate = ?, ModifiedBy = ?"
				+ " where branchid = " + entity.getBranchId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getParentId());
		pstmt.setLong(i++, entity.getBranchTypeId());
		pstmt.setString(i++, entity.getBranchCode());
		pstmt.setString(i++, entity.getName());
		pstmt.setString(i++, entity.getMyanmarName());
		pstmt.setString(i++, entity.getPhone());
		pstmt.setString(i++, entity.getFax());
		pstmt.setString(i++, entity.getEmail());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setString(i++, entity.getCity());
		pstmt.setString(i++, entity.getCountry());
		pstmt.setDate(i++,
				new java.sql.Date(entity.getModifiedDate().getTime()));
		pstmt.setLong(i++, entity.getModifiedBy());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where branchid = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public Branch find(long key) throws SQLException {
		Branch l_Branch = null;
		query = getSelectQuery() + " where branchid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_Branch = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Branch;
	}

	@Override
	public ArrayList<Branch> findAll() throws SQLException {
		ArrayList<Branch> l_BranchList = new ArrayList<Branch>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_BranchList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_BranchList;
	}

	@Override
	protected Branch readRecord(ResultSet rs) throws SQLException {
		Branch l_entity = new Branch();
		l_entity.setBranchId(rs.getLong("BranchId"));
		l_entity.setParentId(rs.getLong("ParentId"));
		l_entity.setBranchTypeId(rs.getLong("BranchTypeId"));
		l_entity.setBranchCode(rs.getString("BranchCode"));
		l_entity.setName(rs.getString("Name"));
		l_entity.setMyanmarName(rs.getString("MyanmarName"));
		l_entity.setPhone(rs.getString("Phone"));
		l_entity.setFax(rs.getString("Fax"));
		l_entity.setEmail(rs.getString("Email"));
		l_entity.setAddress(rs.getString("Address"));
		l_entity.setCity(rs.getString("City"));
		l_entity.setCountry(rs.getString("Country"));
		l_entity.setCreatedDate(rs.getDate("CreatedDate"));
		l_entity.setCreatedBy(rs.getLong("CreatedBy"));
		l_entity.setModifiedDate(rs.getDate("ModifiedDate"));
		l_entity.setModifiedBy(rs.getLong("ModifiedBy"));

		return l_entity;
	}

	public BranchDataList getList(Branch cri) throws SQLException {
		BranchDataList l_DataList = new BranchDataList();
		ArrayList<Branch> l_entityList = new ArrayList<Branch>();
		String colbranchID = "";
	    if(cri.getBranchId()!=0)
	    	colbranchID = " and BranchId <> "+cri.getBranchId();
		String l_cri = " and (" + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%') ";
		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by BranchId desc) as [rowno], * from "
				+ table_name + " where 1 = 1" + l_cri+ colbranchID + ") " + table_name
				+ " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecord(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(BranchId) from " + table_name + " where 1 = 1"
				+ l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public Branch findByUserId(long userId) throws SQLException {
		Branch l_Branch = null;
		query = getSelectQuery() + " as a "
				+ "inner join userinbranches as b on a.branchid = b.branchid "
				+ "inner join [user] as c on b.userid = c.userid "
				+ "where c.userid = " + userId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_Branch = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Branch;
	}

	@Override
	public ArrayList<ReferenceData<Long>> getBranchRefList()
			throws SQLException {
		ArrayList<ReferenceData<Long>> l_refList = new ArrayList<ReferenceData<Long>>();
		query = "select branchid, name from " + table_name;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			ReferenceData<Long> l_refData = new ReferenceData<Long>();
			l_refData.setKey(rs.getLong(1));
			l_refData.setValue(rs.getString(2));

			l_refList.add(l_refData);
		}
		rs.close();
		pstmt.close();
		return l_refList;
	}

	/** MBC **/
	@Override
	public ResultSet prepareBranchReport(BranchRpt info) throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_BranchRpt ?, ?, ?, ?, ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		BranchRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}

	private static void BranchRptParameter(CallableStatement l_pstmt,
			BranchRpt info) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, info.getFromBranchID());
		l_pstmt.setString(i++, info.getToBranchID());
		l_pstmt.setString(i++, info.getFromCountry());
		l_pstmt.setString(i++, info.getToCountry());
		l_pstmt.setString(i++, info.getFromType());
		l_pstmt.setString(i++, info.getToType());
		l_pstmt.setString(i++, info.getOrderby());
		

	}

	@Override
	public Branch findBranchByCode(String code) throws SQLException {
		Branch l_Branch = null;
		query = getSelectQuery() + " where BranchCode = '" + code + "'";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_Branch = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Branch;
	}

}
